package com.example.budget3.util;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.budget3.data.ExpenseCategory;
import com.example.budget3.data.IncomeCategory;
import com.example.budget3.data.TransactionData;
import com.example.budget3.util.TransactionDataContract.BillEntry;
import com.example.budget3.util.TransactionDataContract.DataEntry;
import com.example.budget3.util.TransactionDataContract.TransactionDataDbHelper;

public class DatabaseQueryHandler {
	//query to get the oldest item in the data table
	private static final String OLDEST_TRANSACTION_QUERY = "SELECT * FROM " + DataEntry.TABLE_NAME + " ORDER BY " + DataEntry.COLUMN_NAME_TIME + " ASC LIMIT 1;";
	//query to get all the bills ordered by date of next bill
	private static final String ALL_BILLS_DESC_QUERY = "SELECT * FROM " + BillEntry.TABLE_NAME + " ORDER BY " + BillEntry.COLUMN_NAME_NEXT_TIME + " DESC;";
	//query to get all the bills ordered by date of next bill
	private static final String ALL_BILLS_ASC_QUERY = "SELECT * FROM " + BillEntry.TABLE_NAME + " ORDER BY " + BillEntry.COLUMN_NAME_NEXT_TIME + " ASC;";

	public static int numberOfMonths(Context context, Calendar now) {
		//looks at oldest transaction and how many months there are between then and now
		Calendar oldestTransaction = getOldestTransaction(context);
		if(oldestTransaction != null){
			return monthsBetween(now, oldestTransaction);
		}
		return 0;
	}
	
	
	public static List<TransactionData> getAllTransactions(Context context, Calendar latest, int month){
		 ArrayList<TransactionData> localData = new ArrayList<TransactionData>();
			SQLiteDatabase db = getDataBase(context);
			
			// Define a projection that specifies which columns from the database
			// you will actually use after this query.

			String[] selectionArgs = new String[0];
			Calendar cal = Calendar.getInstance();
			cal.setTime(latest.getTime());
			cal.add(Calendar.MONTH, -1 * month);
			
			String year1 = new SimpleDateFormat("yyyy", Locale.getDefault()).format(cal.getTime());
			String month1 = new SimpleDateFormat("MM",Locale.getDefault()).format(cal.getTime());
			cal.add(Calendar.MONTH, 1);
			String year2 = new SimpleDateFormat("yyyy",Locale.getDefault()).format(cal.getTime());
			String month2 = new SimpleDateFormat("MM",Locale.getDefault()).format(cal.getTime());
			String sql = "SELECT * from "+DataEntry.TABLE_NAME+" where "+DataEntry.COLUMN_NAME_TIME+" >= datetime('"+year1+"-"+month1+"-01 00:00:00') AND "+DataEntry.COLUMN_NAME_TIME+" <  datetime('"+year2+"-"+month2+"-01 00:00:00')"   +"ORDER BY datetime("+DataEntry.COLUMN_NAME_TIME+") DESC";
			Cursor mycursor = db.rawQuery(sql, selectionArgs);
			
			if (mycursor.moveToFirst())
			{
		        do {      
		    		String name = mycursor.getString(
		    				mycursor.getColumnIndexOrThrow(DataEntry.COLUMN_NAME_NAME));
		    		float cost = mycursor.getFloat(
		    				mycursor.getColumnIndexOrThrow(DataEntry.COLUMN_NAME_COST));
		    		int catOrd = mycursor.getInt(
		    				mycursor.getColumnIndexOrThrow(DataEntry.COLUMN_NAME_CATEGORY));
		    		
		    		
		    		String time = mycursor.getString(
		    				mycursor.getColumnIndexOrThrow(DataEntry.COLUMN_NAME_TIME));
		    		int id = mycursor.getInt(
		    				mycursor.getColumnIndexOrThrow(DataEntry._ID));
		    		
		    		Date timeDate;
					try {
						timeDate = UtilMethods.stringToDate(time);
						boolean type = mycursor.getInt(
		    				mycursor.getColumnIndexOrThrow(DataEntry.COLUMN_NAME_TYPE)) == 0 ? false:true;
						if(type){
							ExpenseCategory category = ExpenseCategory.values()[catOrd];
							localData.add( new TransactionData(id, name, cost, category, timeDate, type));
						}else{
							IncomeCategory category = IncomeCategory.values()[catOrd];
							localData.add( new TransactionData(id, name, cost, category, timeDate, type));
						}
						
						
					} catch (ParseException e) {
					}
		        } while (mycursor.moveToNext());
			}
			return localData;
	}
	
	
	public static void removeTransaction(Context context, TransactionData item){
		SQLiteDatabase db = getDataBase(context);
		String selection = DataEntry._ID + " = ?";
		// Specify arguments in placeholder order.
		String[] selectionArgs = { String.valueOf(item.getID()) };
		// Issue SQL statement.
		db.delete(DataEntry.TABLE_NAME, selection, selectionArgs);
	}

	public static Calendar getOldestTransaction(Context context){
		SQLiteDatabase db = getDataBase(context);
		//queries database
		Cursor oldestCursor = db.rawQuery(OLDEST_TRANSACTION_QUERY, null);
		//if false then no items in transactions database
		if (oldestCursor.moveToFirst()) {
			//date of oldest transaction
			String oldDateString = oldestCursor.getString(oldestCursor.getColumnIndexOrThrow(DataEntry.COLUMN_NAME_TIME));
			try{
				return UtilMethods.stringToCalendar(oldDateString);
			}catch(ParseException e){}
		}
		return null;
	}
	
	public static void checkForBillsUpdate(Context context) {
		SQLiteDatabase db = getDataBase(context);

		Cursor mycursor = db.rawQuery(ALL_BILLS_DESC_QUERY, null);
		
		if (mycursor.moveToFirst()) {
			do {
				String name = mycursor.getString(mycursor
						.getColumnIndexOrThrow(BillEntry.COLUMN_NAME_NAME));
				float cost = mycursor.getFloat(mycursor
						.getColumnIndexOrThrow(BillEntry.COLUMN_NAME_COST));
				int catOrd = mycursor.getInt(mycursor
						.getColumnIndexOrThrow(BillEntry.COLUMN_NAME_CATEGORY));
				int period = mycursor.getInt(mycursor
						.getColumnIndexOrThrow(BillEntry.COLUMN_NAME_PERIOD));
				int dayOfMonth = mycursor
						.getInt(mycursor
								.getColumnIndexOrThrow(BillEntry.COLUMN_NAME_DAY_OF_MONTH));
				int id = mycursor.getInt(mycursor
						.getColumnIndexOrThrow(BillEntry._ID));
				boolean type = mycursor.getInt(mycursor
						.getColumnIndexOrThrow(BillEntry.COLUMN_NAME_TYPE)) == 0 ? false
						: true;
				String nextTime = mycursor
						.getString(mycursor
								.getColumnIndexOrThrow(BillEntry.COLUMN_NAME_NEXT_TIME));

				try {
					Date timeDate = UtilMethods.stringToDate(nextTime);
					Calendar instance = Calendar.getInstance();
					instance.setTime(timeDate);
					Calendar now = Calendar.getInstance();
					Date nextTimeDate = null;
					while (instance.before(now)) {
						if (period == 0) {
							instance.add(Calendar.DATE, 1);
							ContentValues values1 = new ContentValues();
							nextTimeDate = instance.getTime();
							values1.put(BillEntry.COLUMN_NAME_NEXT_TIME,
									UtilMethods.dateToString(nextTimeDate));
							db.update(BillEntry.TABLE_NAME, values1,
									BillEntry._ID + "=" + id, null);
						} else if (period == 1) {
							instance.add(Calendar.DATE, 7);
							ContentValues values1 = new ContentValues();
							nextTimeDate = instance.getTime();
							values1.put(BillEntry.COLUMN_NAME_NEXT_TIME,
									UtilMethods.dateToString(nextTimeDate));
							db.update(BillEntry.TABLE_NAME, values1,
									BillEntry._ID + "=" + id, null);
						} else {

							instance.add(Calendar.MONTH, 1);

							int daysInMonth = instance
									.getActualMaximum(Calendar.DAY_OF_MONTH);
							if (daysInMonth >= dayOfMonth) {
								instance.set(Calendar.DAY_OF_MONTH, dayOfMonth);
							} else {
								instance.set(Calendar.DAY_OF_MONTH, daysInMonth);
							}
							ContentValues values1 = new ContentValues();
							nextTimeDate = instance.getTime();
							values1.put(BillEntry.COLUMN_NAME_NEXT_TIME,
									UtilMethods.dateToString(nextTimeDate));
							db.update(BillEntry.TABLE_NAME, values1,
									BillEntry._ID + "=" + id, null);
						}
						if (type) {
							ExpenseCategory category = ExpenseCategory.values()[catOrd];
							dataAdd(db, new TransactionData(id, name, cost,
									category, timeDate, type));
						} else {
							IncomeCategory category = IncomeCategory.values()[catOrd];
							dataAdd(db, new TransactionData(id, name, cost,
									category, timeDate, type));
						}
						timeDate = nextTimeDate;
					}
				} catch (ParseException e) {
				}
			} while (mycursor.moveToNext());
		}
	}
	
	public static long nextBillOrHalfHour(Context context){
		
		long untilNext = nextHalfHour();
		SQLiteDatabase db = getDataBase(context);

		Cursor mycursor = db.rawQuery(ALL_BILLS_ASC_QUERY, null);
		if (mycursor.moveToFirst()) {
			do {
				String nextTime = mycursor.getString(mycursor.getColumnIndexOrThrow(BillEntry.COLUMN_NAME_NEXT_TIME));

				try {
					Date timeDate = UtilMethods.stringToDate(nextTime);

					if (timeDate != null && timeDate.after(new Date())) {
						long untilNextRecurring = (timeDate.getTime() - new Date()
								.getTime());
						if (untilNextRecurring < untilNext) {
							untilNext = untilNextRecurring;
						}
					}
				} catch (ParseException e) {
				}
			} while (mycursor.moveToNext());
		}
		return untilNext;
	}

	private static SQLiteDatabase getDataBase(Context context) {
		TransactionDataContract contract = new TransactionDataContract();
		TransactionDataDbHelper mDbHelper = contract.new TransactionDataDbHelper(context);
		SQLiteDatabase db = mDbHelper.getWritableDatabase();
		return db;
	}
	
	private static void dataAdd(SQLiteDatabase db, TransactionData transactionData) {
		ContentValues values = new ContentValues();

		values.put(DataEntry.COLUMN_NAME_NAME, transactionData.getName());
		values.put(DataEntry.COLUMN_NAME_COST, transactionData.getCost());
		values.put(DataEntry.COLUMN_NAME_CATEGORY,
				transactionData.isExpenseType() ? transactionData.getExpenseCategory()
						.ordinal() : transactionData.getIncomeCategory()
						.ordinal());
		values.put(DataEntry.COLUMN_NAME_TIME,
				UtilMethods.dateToString(transactionData.getTimeDate()));
		values.put(DataEntry.COLUMN_NAME_TYPE, transactionData.isExpenseType());

		// Insert the new row, returning the primary key value of the new row
		db.insert(DataEntry.TABLE_NAME, DataEntry.COLUMN_NAME_NULLABLE, values);
	}
	
	private static long nextHalfHour() {
		Calendar c = Calendar.getInstance();
		Calendar now = Calendar.getInstance();
		c.set(Calendar.MINUTE, 30);
		c.set(Calendar.SECOND, 0);
		c.set(Calendar.MILLISECOND, 0);
		if (c.before(now)) {
			c.add(Calendar.HOUR, 1);
			c.set(Calendar.MINUTE, 0);
		}
		return c.getTimeInMillis() - now.getTimeInMillis();
	}
	
	private static int monthsBetween(Calendar now, Calendar startCalendar) {
		int diffYear = now.get(Calendar.YEAR) - startCalendar.get(Calendar.YEAR);
		int diffMonth = diffYear * 12 + now.get(Calendar.MONTH) - startCalendar.get(Calendar.MONTH);
		return diffMonth + 1;
	}
}
